﻿using System;
using System.Data.SqlClient;
using System.Data;
using prjKQXS_DLL;

namespace prjKQXS_DLL
{
    class NhomVT_DAL
    {
        private SqlConnection conn = null;
        private SqlCommand comm = null;
        private SqlDataAdapter adapter = null;
        private DataTable dt = null;

        public DataTable loadDataList(string tableName,bool isPublic)
        {
            try
            {
                dt = new DataTable();
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                comm = new SqlCommand("select * from [" + tableName + "] where Ma_Nhom is Not NULL and isPublic = @isPublic  order by ma_nhom", conn);
                comm.Parameters.AddWithValue("@isPublic", isPublic);
                adapter = new SqlDataAdapter(comm);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable loadData_NganhHoc(string tableName, int isPublic)
        {
            try
            {
                dt = new DataTable();
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                comm = new SqlCommand("select * from [" + tableName + "] where isPublic = @isPublic and ID_NGHE is NOT NULL", conn);
                comm.Parameters.AddWithValue("@isPublic", isPublic);
                adapter = new SqlDataAdapter(comm);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable loadData(string tableName, int isPublic)
        {
            try
            {
                dt = new DataTable();
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                comm = new SqlCommand("select * from [" + tableName + "] where isPublic = @isPublic ", conn);
                comm.Parameters.AddWithValue("@isPublic", isPublic);
                adapter = new SqlDataAdapter(comm);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable loadData_ByID(string tableName,string id, int isPublic)
        {
            try
            {
                dt = new DataTable();
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                if (id == "")
                {
                    comm = new SqlCommand("select * from [" + tableName + "] where isPublic = @isPublic  and ID_NGANHHOC is not null", conn);
                    comm.Parameters.AddWithValue("@isPublic", isPublic);                    
                }
                else
                {
                    comm = new SqlCommand("select * from [" + tableName + "] where ID_NGANHHOC=@id and isPublic = @isPublic ", conn);
                    comm.Parameters.AddWithValue("@isPublic", isPublic);
                    comm.Parameters.AddWithValue("@id", id);
                }
                adapter = new SqlDataAdapter(comm);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Lay ve Tất cả các nhóm với điều kiện KHÔNG PHẢI LÀ NHÓM CHA
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable loadDataListOnlyChild(string tableName)
        {
            try
            {
                dt = new DataTable();
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                //comm = new SqlCommand("select * from " + tableName + " where isPublic = 1 order by ma_nhom ", conn);
                comm = new SqlCommand("select * from [" + tableName + "] where ID not in(select IdCha from " + tableName + ") and Ma_Nhom is Not NULL and isPublic = 1  order by ma_nhom", conn);
                adapter = new SqlDataAdapter(comm);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }
        public int dropNhomVT(string sotk)
        {
            try
            {
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                comm = new SqlCommand("drop table NhomVT" + sotk, conn);
                return Convert.ToInt32(comm.ExecuteNonQuery());
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public int deleteRow(string sotk, string manhom)
        {
            try
            {
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                string tenbang = "NhomVT" + sotk;
                return clsCommon.deleteRestoreRow(tenbang, "ma_nhom", manhom, false, false);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public int insertRow(string tableName, string columnName, int dodai, string sotk, int year, string manhom)
        {
            try
            {
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                string mamoi = clsCommon.laymadt(tableName, columnName, dodai);                    
                    if (!manhom.Equals(""))
                    {
                        comm = new SqlCommand("select ID from [" + tableName + "] where Ma_Nhom = @Ma_Nhom and isPublic = 1", conn);
                        comm.Parameters.AddWithValue("@Ma_Nhom", manhom);
                        string s = "0";
                        try
                        {
                            s = comm.ExecuteScalar().ToString();
                        }
                        catch
                        {
                            s = "0";
                        }
                        int aa = 0;
                        int idcha = 0;
                        if (Int32.TryParse(s, out aa))
                        {
                            idcha = Int32.Parse(s);
                        }
                        comm = new SqlCommand("insert into [" + tableName + "](Ma_Nhom,Ten_Nhom,Nam,Idcha,isPublic) values(@manhom,@tennhom,@Nam,@IdCha,@isPublic)", conn);
                        comm.Parameters.Add("@manhom", SqlDbType.NVarChar, 20).Value = mamoi;
                        comm.Parameters.Add("@tennhom", SqlDbType.NVarChar, 50).Value = "Thêm nhóm mới " + mamoi;
                        comm.Parameters.AddWithValue("@Nam", year);
                        comm.Parameters.AddWithValue("@IdCha", idcha);
                        comm.Parameters.AddWithValue("@isPublic", 1);
                        return Convert.ToInt32(comm.ExecuteNonQuery());
                    }
                    else
                    {
                        comm = new SqlCommand("insert into [" + tableName + "](Ma_Nhom,Ten_Nhom,Nam,Idcha,isPublic) values(@manhom,@tennhom,@Nam,@IdCha,@isPublic)", conn);
                        comm.Parameters.Add("@manhom", SqlDbType.NVarChar, 20).Value = mamoi;
                        comm.Parameters.Add("@tennhom", SqlDbType.NVarChar, 50).Value = "Thêm nhóm mới " + mamoi;
                        comm.Parameters.AddWithValue("@Nam", year);
                        comm.Parameters.AddWithValue("@IdCha", 0);
                        comm.Parameters.AddWithValue("@isPublic", 1);
                        return Convert.ToInt32(comm.ExecuteNonQuery());
                    }
                
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable searchRow(string sotk, string chuoiloc)
        {
            try
            {
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                if (chuoiloc.Trim().Length > 0)
                {
                    comm = new SqlCommand("select * from NhomVT" + sotk + " where isPublic=1 ma_nhom like '%" + chuoiloc + "%' or ten_nhom like '%" + chuoiloc + "%' order by ma_nhom ", conn);
                }
                else
                {
                    comm = new SqlCommand("select * from NhomVT" + sotk + " where isPublic=1 order by ma_nhom ", conn);
                }
                adapter = new SqlDataAdapter(comm);
                dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public int updateRow(string sotk, string manhom, string tennhom, int year,int id)
        {
            try
            {
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                    comm = new SqlCommand("update NhomVT" + sotk + " set ten_nhom=@tennhom, nam = @nam , ma_nhom=@manhom Where ID=@ID", conn);
                    comm.Parameters.Add("@tennhom", SqlDbType.NVarChar, 255).Value = tennhom;
                    comm.Parameters.Add("@manhom", SqlDbType.NVarChar, 20).Value = manhom;
                    comm.Parameters.AddWithValue("@nam", year);
                    comm.Parameters.AddWithValue("@ID", id);
                    return Convert.ToInt32(comm.ExecuteNonQuery());
                
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public int updateRow(string manhom, string tennhom, int year)
        {
            try
            {
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                comm = new SqlCommand("update tblNHOMMONHOC set ten_nhom=@tennhom, nam = @nam where ma_nhom=@manhom", conn);
                comm.Parameters.Add("@tennhom", SqlDbType.NVarChar, 255).Value = tennhom;
                comm.Parameters.Add("@manhom", SqlDbType.NVarChar, 20).Value = manhom;
                comm.Parameters.AddWithValue("@nam", year);
                return Convert.ToInt32(comm.ExecuteNonQuery());

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
